namespace SampleDbAccess
{
    using SampleDbAccess.DbContext;
    using SampleDbAccess.Model;
    using SampleDbAccess.Repository;
    using Stee.CAP8.DBAccess;
    using System;
    using System.Collections.Generic;
    using System.Globalization;
    using System.Linq.Expressions;
    using System.Threading.Tasks;

    internal static class Program
    {
        public static void Main()
        {
            MainAsync().Wait();
            
        }
        public static async Task MainAsync()
        {
            var continueProgram = true;
            while (continueProgram)
            {
                Console.WriteLine(@"1- AddSample
2- AddRangeIEnumSample 
3- AddRangeParamT
4- GetListSample 
5- GetListAndDeleteSample 
6- PaginationSample 
7- SingleSample
8- SingleOrDefaultSample 
9- UpdateSample 
10- UpdateByPropertyNameSample 
11- DeleteSample 
12- DeleteByIDSample 
13- TransactionSample 
14- RollbackSample
15- CustomAddSample
16- AddSubjectSample
17- GetListSubjectSample
18- DeleteSubjectSample
19- DeleteByIDSubjectSample
20- AddSampleWithConnectionStr

Choose a number to execute the method");

                var input = Console.ReadLine();
                var id = "";
                switch (input)
                {
                    case "1":
                        await AddSample().ConfigureAwait(false);
                        break;
                    case "2":
                        await AddRangeIEnumSample().ConfigureAwait(false);
                        break;
                    case "3":
                        await AddRangeParamTSample().ConfigureAwait(false);
                        break;
                    case "4":
                        await GetListSample().ConfigureAwait(false);
                        break;
                    case "5":
                        await GetListAndDeleteSample().ConfigureAwait(false);
                        break;
                    case "6":
                        Console.WriteLine("Please input the pagination page index");
                        var PageIndex = int.Parse(Console.ReadLine(), CultureInfo.InvariantCulture);
                        Console.WriteLine("Please input the pagination page size");
                        var PageSize = int.Parse(Console.ReadLine(), CultureInfo.InvariantCulture);
                        await PaginationSample(null, PageIndex, PageSize).ConfigureAwait(false);
                        break;
                    case "7":
                        Console.WriteLine("Please input the ID of the record");
                        id = Console.ReadLine();
                        await SingleSample(id).ConfigureAwait(false);
                        break;
                    case "8":
                        Console.WriteLine("Please input the ID of the record");
                        id = Console.ReadLine();
                        await SingleOrDefaultSample(id).ConfigureAwait(false);
                        break;
                    case "9":
                        Console.WriteLine("Please input the ID of the record");
                        id = Console.ReadLine();
                        await UpdateSample(id).ConfigureAwait(false);
                        break;
                    case "10":
                        Console.WriteLine("Please input the ID of the record");
                        id = Console.ReadLine();
                        Console.WriteLine("Please input the property Name");
                        var PropertyName = Console.ReadLine();
                        Console.WriteLine("Please input new property value");
                        var PropertyValue = Console.ReadLine();
                        await UpdatePropertySample(id, PropertyName, PropertyValue).ConfigureAwait(false);
                        break;
                    case "11":
                        Console.WriteLine("Please input the ID of the record");
                        id = Console.ReadLine();
                        await DeleteSample(id).ConfigureAwait(false);
                        break;
                    case "12":
                        Console.WriteLine("Please input the ID of the record");
                        id = Console.ReadLine();
                        await DeleteByIDSample(id).ConfigureAwait(false);
                        break;
                    case "13":
                        await TransactionSample().ConfigureAwait(false);
                        break;
                    case "14":
                        await RollbackSample().ConfigureAwait(false);
                        break;
                    case "15":
                        await CustomAddSample().ConfigureAwait(false);
                        break;
                    case "16":
                        await AddSubjectSample().ConfigureAwait(false);
                        break;
                    case "17":
                        await GetListSubjectSample().ConfigureAwait(false);
                        break;
                    case "18":
                        Console.WriteLine("Please input the ID of the record");
                        id = Console.ReadLine();
                        await DeleteSubjectSample(id).ConfigureAwait(false);
                        break;
                    case "19":
                        Console.WriteLine("Please input the ID of the record");
                        id = Console.ReadLine();
                        await DeleteByIDSubjectSample(id).ConfigureAwait(false);
                        break;
                    case "20":
                        Console.WriteLine("Please input connection string for database (i.e : \"Server=(local);Database=SampleDb2;Trusted_Connection=True;\")");
                        var conStr = Console.ReadLine();
                        Console.WriteLine("Please indicate if the program is using Oracle [Y] or SQL [N] database");
                        var useOracle = Console.ReadLine().ToUpper(CultureInfo.InvariantCulture) == "Y";
                        await AddSampleWithConnectionStr(conStr, useOracle).ConfigureAwait(false);
                        break;
                    default:
                        break;
                }
                Console.WriteLine("Do you want to try another method? [Y/N]");
                continueProgram = Console.ReadLine().ToUpper(CultureInfo.InvariantCulture) == "Y";
            }
        }
        private static async Task AddSample()
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    var std = new Student { Name = "Billy", Gender = "Male" };
                    sRepo.Add(std);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);
                    Console.WriteLine("A record has been added (Name: " + std.Name + ", Gender:" + std.Gender + ")");
                }
            }
        }
        private static async Task CustomAddSample()
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    var std = new Student { Name = "Billy", Gender = "Male" };
                    sRepo.CustomAdd(std);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);
                    Console.WriteLine("A record has been added (Name: " + std.Name + ", Gender:" + std.Gender + ")");
                }
            }
        }
        private static async Task AddSubjectSample()
        {
            using (var sContext = new SubjectContext())
            {
                using (var sRepo = new Repository<Subject>(sContext))
                {
                    var subj = new Subject { Name = "Science", TaughtBy = "SampleDbAccess ConsoleApp" };
                    sRepo.Add(subj);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);
                    Console.WriteLine("A record has been added (Name: " + subj.Name + ", Gender:" + subj.TaughtBy + ")");
                }
            }
        }
        private static async Task AddSampleWithConnectionStr(string connectionStr, bool useOracle)
        {
            using (var sContext = new StudentContext(connectionStr, useOracle))
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    var std = new Student { Name = "Billy", Gender = "Male" };
                    sRepo.Add(std);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);
                    Console.WriteLine("A record has been added (Name: " + std.Name + ", Gender:" + std.Gender + ")");
                }
            }
        }
        private static async Task AddRangeIEnumSample()
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    var std = new List<Student>();
                    for (var count = 0; count < 20; count++)
                    {
                        if (count < 10)
                        {
                            std.Add(new Student { Name = "0" + count.ToString(CultureInfo.InvariantCulture), Gender = "AddRangeIEnumSample" });
                        }
                        else
                        {
                            std.Add(new Student { Name = count.ToString(CultureInfo.InvariantCulture), Gender = "AddRangeIEnumSample" });

                        }
                    }
                    sRepo.AddRange(std);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);
                    Console.WriteLine("A list of 20 records has been added via AddRangeIEnumSample.");
                }
            }
        }
        private static async Task AddRangeParamTSample()
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    var std =
                        new Student { Name = "ParamT1", Gender = "AddRangeParamTSample" };
                    var std2 =
                        new Student { Name = "ParamT2", Gender = "AddRangeParamTSample" };
                    sRepo.AddRange(std, std2);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);

                    Console.WriteLine("2 records has been added via AddRangeParamTSample.");
                }
            }
        }
        private static async Task GetListSample(Expression<Func<Student, bool>> condition = null, IList<OrderBy> orderBy = null)
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    var result = await sRepo.GetListAsync(condition, orderBy, activeDatastate: false).ConfigureAwait(false);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);

                    foreach (var s in result)
                    {
                        Console.WriteLine("Guid(" + s.ID.ToString(CultureInfo.InvariantCulture) + "), Name(" + s.Name + "), Gender(" + s.Gender + ") ");
                    }
                }
            }
        }
        private static async Task GetListAndDeleteSample()
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    var result = await sRepo.GetListAsync(activeDatastate:false).ConfigureAwait(false);
                    foreach (var s in result)
                    {
                        sRepo.Delete(s, false);
                    }
                    await sContext.SaveChangesAsync().ConfigureAwait(false);
                    Console.WriteLine("Delete All: SUCCESS");
                }
            }
        }
        private static async Task GetListSubjectSample(Expression<Func<Subject, bool>> condition = null, IList<OrderBy> orderBy = null)
        {
            using (var sContext = new SubjectContext())
            {
                using (var sRepo = new Repository<Subject>(sContext))
                {
                    var result = await sRepo.GetListAsync(condition, orderBy, activeDatastate: false).ConfigureAwait(false);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);

                    foreach (var s in result)
                    {
                        Console.WriteLine("Guid(" + s.ID.ToString(CultureInfo.InvariantCulture) + "), Name(" + s.Name + "), TaughtBy(" + s.TaughtBy + ") ");
                    }
                }
            }
        }
        private static async Task PaginationSample(Expression<Func<Student, bool>> condition = null, int pageIndex = 1, int pageSize = 10000)
        {
            var orderBy = new List<OrderBy>
            {
                new OrderBy { PropertyName = "Name" }
            };
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    var result = sRepo.GetList(condition, orderBy, pageIndex, pageSize);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);

                    foreach (var s in result)
                    {
                        Console.WriteLine(s.Name);
                    }
                }
            }
        }
        private static async Task SingleSample(string id)
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    var result = await sRepo.GetSingleAsync(long.Parse(id, CultureInfo.InvariantCulture)).ConfigureAwait(false);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);

                    Console.WriteLine("The Sample named " + result.Name + " is " + result.Gender + ".");
                }
            }
        }
        private static async Task SingleOrDefaultSample(string id)
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {

                    var result = await sRepo.GetSingleOrDefaultAsync(long.Parse(id, CultureInfo.InvariantCulture)).ConfigureAwait(false);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);

                    var message = "There is no record found.";
                    if (result != null)
                    {
                        message = "The Sample named " + result.Name + " is " + result.Gender + ".";
                    }
                    Console.WriteLine(message);
                }
            }
        }
        private static async Task UpdateSample(string id)
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    var result = sRepo.GetSingleOrDefault(long.Parse(id, CultureInfo.InvariantCulture));
                    var message = "There is no record found.";

                    if (result == null)
                    {
                        Console.WriteLine(message);
                        return;
                    }
                    result.Name += " (Updated)";
                    sRepo.Update(result);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);
                    message = "The Sample named " + result.Name + " is updated.";
                    Console.WriteLine(message);
                }
            }
        }
        private static async Task UpdatePropertySample(string id, string propertyName, string propertyValue)
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {

                    sRepo.UpdateProperty(long.Parse(id, CultureInfo.InvariantCulture), propertyName, propertyValue);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);

                    Console.WriteLine("Updated property by " + id);
                }
            }
        }
        private static async Task DeleteSample(string id = "")
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    var result = sRepo.GetSingleOrDefault(long.Parse(id, CultureInfo.InvariantCulture));
                    sRepo.Delete(result);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);

                    Console.WriteLine("Deleted record (" + id + ")");
                }
            }
        }
        private static async Task DeleteByIDSample(string id = "")
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    sRepo.DeleteByID(long.Parse(id, CultureInfo.InvariantCulture));
                    await sContext.SaveChangesAsync().ConfigureAwait(false);

                    Console.WriteLine("Deleted record (" + id + ")");
                }
            }
        }
        private static async Task RollbackSample()
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    sContext.BeginTransaction();
                    try
                    {
                        await GetListAndDeleteSampleRB(sRepo, sContext).ConfigureAwait(false);
                        AddRangeIEnumAndExceptionSample(sRepo);
                        await sContext.CommitTransactionAsync().ConfigureAwait(false);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.ToString());
                        Console.WriteLine("Initializing rollback");
                        sContext.RollbackTransaction();
                    }
                }
            }
        }
        private static async Task GetListAndDeleteSampleRB(StudentRepository sRepo, StudentContext sContext)
        {
            var result = sRepo.GetList();
            foreach (var s in result)
            {
                sRepo.Delete(s);
            }
            await sContext.SaveChangesAsync().ConfigureAwait(false);
            Console.WriteLine("Delete All: SUCCESS");
        }
        private static void AddRangeIEnumAndExceptionSample(StudentRepository sRepo)
        {
            var std = new List<Student>();
            for (var count = 1; count < 15; count++)
            {
                if (count < 10)
                {
                    std.Add(new Student { Name = "0" + count.ToString(CultureInfo.InvariantCulture), Gender = "TRANSACTION TEST" });
                }
                else
                {
                    std.Add(new Student { Name = count.ToString(CultureInfo.InvariantCulture), Gender = "TRANSACTION TEST" });

                }
            }
            sRepo.AddRange(std);
            Console.WriteLine("Added new set: FAILED");
            // To simulate an exception thrown, therefore this will cause warning for sonar
            throw new Exception();
        }
        private static async Task TransactionSample()
        {
            using (var sContext = new StudentContext())
            {
                using (var sRepo = new StudentRepository(sContext))
                {
                    sContext.BeginTransaction();
                    try
                    {
                        AddRangeIEnumNoTransSample(sRepo);
                        AddRangeIEnumNoTransSample(sRepo);
                        await sContext.CommitTransactionAsync().ConfigureAwait(false);
                    }
                    catch (MultipleTransactionException ex)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.ToString());
                        Console.WriteLine("Initializing rollback");
                        sContext.RollbackTransaction();
                    }
                }
            }
        }
        private static void AddRangeIEnumNoTransSample(StudentRepository sRepo)
        {
            var std = new List<Student>();
            for (var count = 1; count < 20; count++)
            {
                if (count < 10)
                {
                    std.Add(new Student { Name = "0" + count.ToString(CultureInfo.InvariantCulture), Gender = "AddRangeIEnumNoTransSample TEST" });
                }
                else
                {
                    std.Add(new Student { Name = count.ToString(CultureInfo.InvariantCulture), Gender = "AddRangeIEnumNoTransSample TEST" });

                }
            }
            sRepo.AddRange(std);
            Console.WriteLine("Added new set: SUCCESS");
        }
        private static async Task DeleteSubjectSample(string id = "")
        {
            using (var sContext = new SubjectContext())
            {
                using (var sRepo = new Repository<Subject>(sContext))
                {
                    var result = sRepo.GetSingleOrDefault(long.Parse(id, CultureInfo.InvariantCulture));
                    sRepo.Delete(result, false);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);

                    Console.WriteLine("Deleted record (" + id + ")");
                }
            }
        }
        private static async Task DeleteByIDSubjectSample(string id = "")
        {
            using (var sContext = new SubjectContext())
            {
                using (var sRepo = new Repository<Subject>(sContext))
                {
                    sRepo.DeleteByID(long.Parse(id, CultureInfo.InvariantCulture), false);
                    await sContext.SaveChangesAsync().ConfigureAwait(false);

                    Console.WriteLine("Deleted record (" + id + ")");
                }
            }
        }
    }
}
